Release 10.1A: OpenEdge Data Management:
SQL Development

Working with SQL column widths

A Progress 4GL database can contain fields, also referred to as columns, of variable length. However, SQL CREATE TABLE statements specify the maximum width of each column in a table. 4GL programs have the ability to insert data whose length exceeds the Data Dictionary value of SQLWidth. SQL applications will not be able to read a row if a column contains data greater than the SQLWidth value defined in the Data Dictionary.

Two tools enable you to compensate for column width discrepancies:

The 4GL -checkwidth startup parameter.
The DBTool utility.

Using the -checkwidth startup parameter

OpenEdge 4GL programmers can override Data Dictionary column definitions in 4GL programs. Therefore, a SQL application cannot read a record if a column contains data greater than the SQLWidth value defined in the Data Dictionary. An attempt to retrieve a column that exceeds the SQLWidth definition generates an error message, and the attempt to access the record fails.

Use the -checkwidth startup parameter to specify whether Progress compares CHARACTER, DECIMAL, and RAW field data against the metaschema _width field value before updating a database record. The _width field value specifies the maximum width of the data allowed in a field.

The syntax for the -checkwidth startup parameter is:

Syntax

-checkwidth n

The -checkwidth startup parameter can be employed in the following modes:

0 — Ignore the _width field value and store the data. This is the default mode.
1 — Store the data and generate a warning message if the data exceeds the size specified in the _width field.
2 — Do not store data that exceeds the size specified in the _width field and generate an error. Specify this mode if you want the Progress 4GL to behave like SQL.

For more information on the -checkwidth startup parameter, see OpenEdge Deployment: Startup Command and Parameter Reference .

Using the DBTool utility

The DBTool utility allows Progress users to identify when the size of column data in the database exceeds the Data Dictionary definition and therefore the SQLWidth value. The DBTool utility addresses this situation because it allows for the fast updating of Data Dictionary SQLWidth definitions.

The following error message is reported to a SQL application when the SQLWidth for a column exceeds the Data Dictionary SQLWidth definition:

Column column in table table has value exceeding its max length or precision (7864)

The syntax for DBTool is:

Syntax

dbtool dbname

To access DBTool from the command line:

Type dbtool and the database name and press Enter. The DBTool option menu appears:



Select an option from the menu and press Enter.

Table 7–2 describes the options available in the DBTool option menu.

Table 7–2: DBTool option menu

Option	Description
1	Finds the maximum field sizes and reports them.
2	Finds the maximum field sizes and updates their widths.
3	Validates the schema versioning of the records after the records are updated by DBTool.
4	Validates the schema versioning before and after the records are updated in DBTool.
5	Validates db keys while scanning database blocks.
9	Enables or disables file logging.
Q	Quits the DBTool utility.

For more information on the DBTool utility, see OpenEdge Data Management: Database Administration .